******SET YOUR DIRECTORY to ...\replication



///////////////////////////////////////////////////////////////////////////////////////////
///TABLE A.15
///////////////////////////////////////////////////////////////////////////////////////////
use "finaldata\final_cc_data.dta", clear

drop if fipsplace_00==.
xtset fipsplace_00 year


g tetra_corr_Y6 = F13.tetra_corr
g tetra_corr_Y8 = F11.tetra_corr
g tetra_corr_Y10 = F9.tetra_corr
g tetra_corr_Y12 = F7.tetra_corr
g tetra_corr_Y14 = F5.tetra_corr

drop if year>1991

*Create instrument and outcome
g good_soil = (ph1_plc_wtm_wtm_0_r>=6.8 & ph1_plc_wtm_wtm_0_r<=7.7)
gen inter_goodtetra=good_soil*tetra_corr
gen inter_badtetra=(1-good_soil)*tetra_corr
gen inter_badtetra_Y6=(1-good_soil)*tetra_corr_Y6



g perc_cc =  tot_cc_oripop_corr / (tot_ncc_oripop_corr + tot_cc_oripop_corr)
g pop_msa = tot_ncc_oripop_corr + tot_cc_oripop_corr

*Create dependent variables
gen vc_pc=totnpcc_cc_offenses_vc*1000
g lnpop_cc = ln(tot_cc_oripop_corr)
g lnpop_ncc = ln(tot_ncc_oripop_corr)
gen lnpop_msa=ln(pop_msa)

*Cluster variables
egen cdivcodeyear=group(cdivcode year)

*Label variables
label var vc_pc "Violent crime"

***RF
label var inter_badtetra "High LB x Lead (19 Years Lag)"
label var inter_badtetra_Y6 "High LB x Lead (Other Lag)"

reghdfe perc_cc inter_badtetra inter_badtetra_Y6, abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
outreg2 inter_badtetra inter_badtetra_Y6 using table_rob_diflags, tex(frag) nocon noni nonotes nor2 label ctitle("Sh. Pop CC") addtext(MSA FE, YES, Year FE, YES, C. reg x Year FE, YES, Other Lag, 6 Years Lag, Estimation, IV) slow(5000) replace
 
drop inter_badtetra_Y6
gen inter_badtetra_Y6=(1-good_soil)*tetra_corr_Y8
label var inter_badtetra_Y6 "High LB x Lead (Other Lag)"

reghdfe perc_cc inter_badtetra inter_badtetra_Y6, abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
outreg2 inter_badtetra inter_badtetra_Y6 using table_rob_diflags, tex(frag) nocon noni nonotes nor2 label ctitle("Sh. Pop CC") addtext(MSA FE, YES, Year FE, YES, C. reg x Year FE, YES, Other Lag, 8 Years Lag, Estimation, IV) slow(5000)
 
drop inter_badtetra_Y6
gen inter_badtetra_Y6=(1-good_soil)*tetra_corr_Y10
label var inter_badtetra_Y6 "High LB x Lead (Other Lag)"

reghdfe perc_cc inter_badtetra inter_badtetra_Y6, abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
outreg2 inter_badtetra inter_badtetra_Y6 using table_rob_diflags, tex(frag) nocon noni nonotes nor2 label ctitle("Sh. Pop CC") addtext(MSA FE, YES, Year FE, YES, C. reg x Year FE, YES, Other Lag, 10 Years Lag, Estimation, IV) slow(5000)
 
drop inter_badtetra_Y6
gen inter_badtetra_Y6=(1-good_soil)*tetra_corr_Y12
label var inter_badtetra_Y6 "High LB x Lead (Other Lag)"

reghdfe perc_cc inter_badtetra inter_badtetra_Y6, abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
outreg2 inter_badtetra inter_badtetra_Y6 using table_rob_diflags, tex(frag) nocon noni nonotes nor2 label ctitle("Sh. Pop CC") addtext(MSA FE, YES, Year FE, YES, C. reg x Year FE, YES, Other Lag, 12 Years Lag, Estimation, IV) slow(5000)





///////////////////////////////////////////////////////////////////////////////////////////
///TABLE A.16 - A.17 & FIGURE A.16
///////////////////////////////////////////////////////////////////////////////////////////
use "finaldata\final_cc_data.dta", clear

drop if fipsplace_00==.

*Merge with school district data
merge 1:1 year fipsplace_00 using "finaldata\data_ucr_plc_sd.dta"

xtset fipsplace_00 year

drop if year>1991

*Create instrument and outcome
g good_soil = (ph1_plc_wtm_wtm_0_r>=6.8 & ph1_plc_wtm_wtm_0_r<=7.7)
gen inter_goodtetra=good_soil*tetra_corr
gen inter_badtetra=(1-good_soil)*tetra_corr
g perc_ncc =  tot_ncc_oripop_corr / (tot_ncc_oripop_corr + tot_cc_oripop_corr)
g pop_msa = tot_ncc_oripop_corr + tot_cc_oripop_corr
g perc_ncc_sd = tot_ncc_sd_oripop_corr / (tot_ncc_sd_oripop_corr + tot_cc_sd_oripop_corr)
g perc_ncc_sdcc = tot_ncc_sdcc_oripop_corr / (tot_ncc_sd_oripop_corr + tot_cc_sd_oripop_corr)
g perc_ncc_nosdcc = tot_ncc_nosdcc_oripop_corr / (tot_ncc_sd_oripop_corr + tot_cc_sd_oripop_corr)
g perc_ncc_allsdcc = tot_ncc_allsdcc_oripop_corr / (tot_ncc_sd_oripop_corr + tot_cc_sd_oripop_corr)

*Create dependent variables
gen vc_pc=totnpcc_cc_offenses_vc*1000
g lnpop_cc = ln(tot_cc_oripop_corr)
g lnpop_ncc = ln(tot_ncc_oripop_corr)
gen lnpop_msa=ln(pop_msa)
gen lnpop_ncc_sd = ln(tot_ncc_sd_oripop_corr)
gen lnpop_ncc_sdcc = ln(tot_ncc_sdcc_oripop_corr)
gen lnpop_ncc_nosdcc = ln(tot_ncc_nosdcc_oripop_corr)
gen lnpop_ncc_allsdcc = ln(tot_ncc_allsdcc_oripop_corr)

*Cluster variables
egen cdivcodeyear=group(cdivcode year)

*Label variables
label var vc_pc "Violent crime"
label var inter_badtetra "High LB x Lead"

***Perc
*Perc NCC, old sample
ivreghdfe perc_ncc (vc_pc = inter_badtetra), abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
local ff= string(round(e(cdf),.01) , "%9.2f")
outreg2 vc_pc using table_perc_sd, tex(frag) nocon noni nor2 nonotes label ctitle("Sh. Pop NCC") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Sample, All, Estimation, IV, F, `ff')  replace
*Perc NCC, school district sample
ivreghdfe perc_ncc_sd (vc_pc = inter_badtetra), abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
local ff= string(round(e(cdf),.01) , "%9.2f")
outreg2 vc_pc using table_perc_sd, tex(frag) nocon noni nor2 nonotes label ctitle("Sh. Pop NCC") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Sample, SD, Estimation, IV, F, `ff')
*Perc NCC sharing school district, school district sample
ivreghdfe perc_ncc_sdcc (vc_pc = inter_badtetra), abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
local ff= string(round(e(cdf),.01) , "%9.2f")
outreg2 vc_pc using table_perc_sd, tex(frag) nocon noni nor2 nonotes label ctitle("Sh. Pop NCC same SD") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Sample, SD, Estimation, IV, F, `ff')
*Perc NCC not sharing school district, school district sample
ivreghdfe perc_ncc_nosdcc (vc_pc = inter_badtetra), abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
local ff= string(round(e(cdf),.01) , "%9.2f")
outreg2 vc_pc using table_perc_sd, tex(frag) nocon noni nor2 nonotes label ctitle("Sh. Pop NCC not same SD") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Sample, SD, Estimation, IV, F, `ff')
*Perc NCC sharing entirely school district, school district sample
ivreghdfe perc_ncc_allsdcc (vc_pc = inter_badtetra), abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
local ff= string(round(e(cdf),.01) , "%9.2f")
outreg2 vc_pc using table_perc_sd, tex(frag) nocon noni nor2 nonotes label ctitle("Sh. Pop NCC all same SD") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Sample, SD, Estimation, IV, F, `ff')



***Pop
*Pop NCC, old sample
ivreghdfe lnpop_ncc (vc_pc = inter_badtetra), abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
local ff= string(round(e(cdf),.01) , "%9.2f")
outreg2 vc_pc using table_pop_sd, tex(frag) nocon noni nor2 nonotes label ctitle("ln(Pop NCC)") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Sample, All, Estimation, IV, F, `ff')  replace
*Pop NCC, school district sample
ivreghdfe lnpop_ncc_sd (vc_pc = inter_badtetra), abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
local ff= string(round(e(cdf),.01) , "%9.2f")
outreg2 vc_pc using table_pop_sd, tex(frag) nocon noni nor2 nonotes label ctitle("ln(Pop NCC)") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Sample, SD, Estimation, IV, F, `ff')
*Pop NCC sharing school district, school district sample
ivreghdfe lnpop_ncc_sdcc (vc_pc = inter_badtetra), abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
local ff= string(round(e(cdf),.01) , "%9.2f")
outreg2 vc_pc using table_pop_sd, tex(frag) nocon noni nor2 nonotes label ctitle("ln(Pop NCC same SD)") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Sample, SD, Estimation, IV, F, `ff')
*Pop NCC not sharing school district, school district sample
ivreghdfe lnpop_ncc_nosdcc (vc_pc = inter_badtetra), abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
local ff= string(round(e(cdf),.01) , "%9.2f")
outreg2 vc_pc using table_pop_sd, tex(frag) nocon noni nor2 nonotes label ctitle("ln(Pop NCC not same SD)") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Sample, SD, Estimation, IV, F, `ff')
*Pop NCC sharing entirely school district, school district sample
ivreghdfe lnpop_ncc_allsdcc (vc_pc = inter_badtetra), abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
local ff= string(round(e(cdf),.01) , "%9.2f")
outreg2 vc_pc using table_pop_sd, tex(frag) nocon noni nor2 nonotes label ctitle("ln(Pop NCC all same SD)") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Sample, SD, Estimation, IV, F, `ff')


***Timing
mat treat_sdcc = J(14,4,1)
mat treat_nosdcc = J(14,4,1)

forvalues i=0/13{
local j=`i'+1
ivreghdfe perc_ncc_sdcc (L`i'.vc_pc = L`i'.inter_badtetra), abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
mat treat_sdcc[`j',1] = `i'
mat treat_sdcc[`j',2] = _b[L`i'.vc_pc]
mat treat_sdcc[`j',3] = _b[L`i'.vc_pc] + _se[L`i'.vc_pc]*invttail(e(N),0.05)
mat treat_sdcc[`j',4] = _b[L`i'.vc_pc] - _se[L`i'.vc_pc]*invttail(e(N),0.05)
ivreghdfe perc_ncc_nosdcc (L`i'.vc_pc = L`i'.inter_badtetra), abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
mat treat_nosdcc[`j',1] = `i'
mat treat_nosdcc[`j',2] = _b[L`i'.vc_pc]
mat treat_nosdcc[`j',3] = _b[L`i'.vc_pc] + _se[L`i'.vc_pc]*invttail(e(N),0.05)
mat treat_nosdcc[`j',4] = _b[L`i'.vc_pc] - _se[L`i'.vc_pc]*invttail(e(N),0.05)
}

g yy_sdcc = treat_sdcc[_n,1] in 1/14
g eff_sdcc = treat_sdcc[_n,2] in 1/14
g eff_sdcc_10 = treat_sdcc[_n,3] in 1/14
g eff_sdcc_90 = treat_sdcc[_n,4] in 1/14
g yy_nosdcc = treat_nosdcc[_n,1] in 1/14
g eff_nosdcc = treat_nosdcc[_n,2] in 1/14
g eff_nosdcc_10 = treat_nosdcc[_n,3] in 1/14
g eff_nosdcc_90 = treat_nosdcc[_n,4] in 1/14

label var eff_sdcc "Coefficient"
label var eff_sdcc_10 "Lowedr bound CI 10"
label var eff_sdcc_90 "Higher bound CI 90"
label var eff_nosdcc "Coefficient"
label var eff_nosdcc_10 "Lower bound CI 10"
label var eff_nosdcc_90 "Higher bound CI 90"

twoway (rcap eff_sdcc_10 eff_sdcc_90 yy_sdcc, yaxis(1) title("NCC sharing school district") ytitle("Coefficient")) (scatter eff_sdcc yy_sdcc, xtitle("Lags") xlabel(0(2)12)) 
graph export figure_sdcc.png, as(png) replace
twoway (rcap eff_nosdcc_10 eff_nosdcc_90 yy_nosdcc, yaxis(1) title("NCC no sharing school district") ytitle("Coefficient")) (scatter eff_nosdcc yy_nosdcc, xtitle("Lags") xlabel(0(2)12)) 
graph export figure_nosdcc.png, as(png) replace









///////////////////////////////////////////////////////////////////////////////////////////
///TABLE A.18
///////////////////////////////////////////////////////////////////////////////////////////
clear
use finaldata\census7080_micro.dta


***Define weights
svyset cluster [pweight=perwt], strata(strata)

***Use only 1980 (in 1970 know only if moves between MSA but not if between CC and sub)
keep if year==1980

***Define suburbanized
gen livemsa=(metro==2|metro==3|metro==4)
g sub=(metro==3)
replace sub=. if metro==0 | metro==1 | metro==4
label var sub "Suburbanized now"
g sub5=2 if (sub==1 & migtype5==3)
replace sub5=1 if (sub==1 & migtype5==4)
replace sub5=0 if sub==0
label var sub5 "Suburbanization today and 5 years ago"
label define sub5l 2 "Sub today, CC past" 1 "Sub today, sub past" 0 "CC today"
label values sub5 sub5l
gen justsub5=(sub==2)

***Generate variables
gen white=(race==1)
gen black=(race==2)
gen married=(marrno==1|marrno==2)
gen hshigher=(educ>=6 & educ!=.)
gen employed=(empstat==1)
gen unemployed=(empstat==2)
gen workcc=(pwtype==1|pwtype==2|pwtype==3)
gen workncc=(pwtype==4)
gen whitehshigher=(white==1&hshigher==1)
replace whitehshigher=. if white==0

***Table with characteristics of suburbanized and people staying in CC
file open myfile using table_sub_demo.txt, write replace
file write myfile "\begin{tabular}{l c c} \hline\hline \textbf{Variable} &  \textbf{Recent suburbanized} &  \textbf{Difference wrt people staying in CC} \\  \hline" _n

local varlist  "age nchild married white black hshigher whitehshigher employed unemployed workncc occscore"
foreach var in `varlist'{

 
if "`var'"=="age"{
 local name = "Mean age"
 }
     else if "`var'"=="nchild"{
 local name = "Mean number children"
 }
    else if "`var'"=="married"{
 local name = "Prop. married"
 }
    else if "`var'"=="white"{
 local name = "Prop. white"
 }
     else if "`var'"=="black"{
 local name = "Prop. black"
 }
   else if "`var'"=="hshigher"{
 local name = "Prop. high school or higher"
 }
    else if "`var'"=="whitehshigher"{
 local name = "Prop. high school or higher of whites"
 }
     else if "`var'"=="employed"{
 local name = "Prop. employed"
 }
      else if "`var'"=="unemployed"{
 local name = "Prop. unemployed"
 }
     else if "`var'"=="workncc"{
 local name = "Prop. people not working in CC"
 }
     else if "`var'"=="occscore"{
 local name = "Mean occupational score"
 }
 else {
 local name = "ERROR"
 }

 svy: reg `var' i.sub5 if livemsa==1
 local aa = _b[_cons]+_b[2.sub5]
 local bb = _b[2.sub5]
 local pval = ttail(e(df_r),abs(_b[2.sub5]/_se[2.sub5]))*2
 if `pval'>0.1  {
	local bb = string(`bb',"%8.2f") 
 } 
 else if `pval'>0.05 { 
	local bb = string(`bb',"%8.2f") + "$^\star$"
 }
  else if `pval'>0.01 { 
	local bb = string(`bb',"%8.2f") + "$^\star$" + "$^\star$"
 }
 else {
 	local bb = string(`bb',"%8.2f") + "$^\star$" + "$^\star$" + "$^\star$"
 }
 

 
  file write myfile ("`name'") _tab "&" %8.2f (`aa') _tab "&"   _tab %8.2f ("`bb'") _tab  "\\" _n 
 
 } 
*

file write myfile "\hline \end{tabular}"
file close myfile





///////////////////////////////////////////////////////////////////////////////////////////
///TABLE A.19 & FIGURE A.17
///////////////////////////////////////////////////////////////////////////////////////////

use "finaldata\final_cc_data.dta", clear

drop if fipsplace_00==.
xtset fipsplace_00 year

drop if year>1991

g good_soil = (ph1_plc_wtm_wtm_0_r>=6.8 & ph1_plc_wtm_wtm_0_r<=7.7)
g bad_soil=(1-good_soil)

gen inter_badtetra=(1-good_soil)*tetra_corr

label var inter_badtetra "High LB x Lead"


replace pcths = pcths / 100
*First stages
reghdfe pcths inter_badtetra, absorb(fipsplace_00 year cdivcode#year)  vce(cluster cdivcode#year)
outreg2 using table_edu_lead, keep(inter_badtetra) tex(frag) nocon noni nonotes label ctitle("Sh. High School CC") addtext(MSA FE, YES, Year FE, YES, C. div x Year FE, YES, Year birth FE, NO, C. div X Year birth FE, NO, Controls, NO, Sample, CC CY60-90, Timing Lead, Lag 19 Years) replace


duplicates drop year, force
rename year year_birth
replace year_birth = year_birth - 19
rename tetra_corr tetra_birth

keep year_birth tetra_birth

tempfile _temp
save `_temp'
 
 
use "finaldata\educ_micro.dta", clear

g high = (educ>=6)

g good_soil = (ph1_plc_wtm_wtm_0_r>=6.8 & ph1_plc_wtm_wtm_0_r<=7.7)
gen inter_badtetra=(1 - good_soil)*tetra_corr
label var inter_badtetra "High LB x Lead"

g foreign_born = (bpl>99 & bpl!=.)


reghdfe high inter_badtetra [w=perwt] if age>=20 & year<1991,a(fipsplace_00 i.year##i.cdivcode) cluster(i.year#i.cdivcode)
outreg2 using table_edu_lead, keep(inter_badtetra) tex(frag) nocon noni nonotes label ctitle("High School") addtext(MSA FE, YES, Year FE, YES, C. div x Year FE, YES, Year birth FE, NO, C. div X Year birth FE, NO, Controls, NO, Sample, Ind. CY60-90, Timing Lead, Lag 19 Years) 

reghdfe high inter_badtetra age [w=perwt] if age>=20 & year<1991,a(fipsplace_00 i.year##i.cdivcode sex  race) cluster(i.year#i.cdivcode)
outreg2 using table_edu_lead, keep(inter_badtetra) tex(frag) nocon noni nonotes label ctitle("High School") addtext(MSA FE, YES, Year FE, YES, C. div x Year FE, YES, Year birth FE, NO, C. div X Year birth FE, NO, Controls, YES, Sample, Ind. CY60-90, Timing Lead, Lag 19 Years) 


g year_birth = year - age

merge n:1 year_birth using `_temp'


drop inter_badtetra

gen inter_badtetra_birth=(1-good_soil)*tetra_birth
label var inter_badtetra "High LB x Lead"

reghdfe high inter_badtetra_birth [w=perwt] if age>=20 , a(fipsplace_00 i.year_birth i.year_birth#i.cdivcode) cluster(i.year_birth#i.cdivcode)
outreg2 using table_edu_lead, keep(inter_badtetra) tex(frag) nocon noni nonotes label ctitle("High School") addtext(MSA FE, YES, Year FE, NO, C. div x Year FE, NO, Year birth FE, YES, C. div X Year birth FE, YES, Controls, NO, Sample, Ind. CY70-00, Timing Lead, Year Birth) 


reghdfe high inter_badtetra_birth age [w=perwt] if age>=20 , a(fipsplace_00 i.year_birth i.year_birth#i.cdivcode sex  race) cluster(i.year_birth#i.cdivcode)
outreg2 using table_edu_lead, keep(inter_badtetra) tex(frag) nocon noni nonotes label ctitle("High School") addtext(MSA FE, YES, Year FE, NO, C. div x Year FE, NO, Year birth FE, YES, C. div X Year birth FE, YES, Controls, YES, Sample, Ind. CY70-00, Timing Lead, Year Birth) 


g stay = (migrate5d ==10 | migrate5d==21 | migrate1d ==10 | migrate1d==21) & foreign==0

reghdfe high inter_badtetra_birth [w=perwt] if age>=20 & stay==1, a(fipsplace_00 i.year_birth i.year_birth#i.cdivcode) cluster(i.year_birth#i.cdivcode)
outreg2 using table_edu_lead, keep(inter_badtetra) tex(frag) nocon noni nonotes label ctitle("High School") addtext(MSA FE, YES, Year FE, NO, C. div x Year FE, NO, Year birth FE, YES, C. div X Year birth FE, YES, Controls, NO, Sample, Ind. Stayers CY70-00, Timing Lead, Year Birth)


reghdfe high inter_badtetra_birth age [w=perwt] if age>=20 & stay==1, a(fipsplace_00 i.year_birth i.year_birth#i.cdivcode sex  race) cluster(i.year_birth#i.cdivcode)
outreg2 using table_edu_lead, keep(inter_badtetra) tex(frag) nocon noni nonotes label ctitle("High School") addtext(MSA FE, YES, Year FE, NO, C. div x Year FE, NO, Year birth FE, YES, C. div X Year birth FE, YES, Controls, YES, Sample, Ind. Stayers CY70-00, Timing Lead, Year Birth)

egen racc_msa=mean(racc), by(fipsplace_00)
gen inter_racc=inter_badtetra_birth*racc_msa

label var racc "Num. Hways"
label var inter_racc "High LB x Lead (birth) X Av. Hways"

reghdfe high inter_badtetra_birth inter_racc age [w=perwt] if age>=20 & stay==1, a(fipsplace_00 i.year_birth i.year_birth#i.cdivcode sex  race) cluster(i.year_birth#i.cdivcode)
outreg2 using table_edu_lead, keep(inter_badtetra inter_racc) tex(frag) nocon noni nor2 nonotes label ctitle("High School") addtext(MSA FE, YES, Year FE, NO, C. div x Year, NO, C. reg x Year, NO, Year birth FE, YES, C. div x Year birth, YES, Ind. controls, YES, Sample, Stayers CY70-00, Estimation, OLS) 


reghdfe high inter_badtetra_birth c.inter_badtetra_birth#c.racc_msa  age [w=perwt] if age>=20 & stay==1, a(fipsplace_00 i.year_birth i.year_birth#i.cdivcode sex  race) cluster(i.year_birth#i.cdivcode)


margins, dydx(inter_badtetra_birth) at(racc_msa=(0(1)7))
marginsplot, xlabel(0 "0: 10th perc." 2 "2: 50th perc." 4 "4: 75th. perc" 6 "6: 95th. perc") yline(0) xtitle("Av. city center highways rays") title("AMEs of High LB x Lead (birth) on educ. attainments with 95% CIs")
graph export figure_edu_lead_het.png, as(png) replace





///////////////////////////////////////////////////////////////////////////////////////////
///Table A.20
///////////////////////////////////////////////////////////////////////////////////////////
clear all
use "finaldata\STATA Goldin.dta", clear

foreach y in "1910" "1920" "1930" "1939"{
foreach var of varlist ageent- contage{
gen `var'_`y'_2=`var' if year==`y'
egen `var'_`y'=max(`var'_`y'_2), by(state)
drop `var'_`y'_2
}
}
drop if year!=1939
keep stfip *_19*
rename stfip STATEFP00

merge 1:m STATEFP00 using "finaldata\final_cc_data.dta"

*FBL: not info Alaska and DC
drop _merge

*Clean data
drop if fipsplace_00==.
xtset fipsplace_00 year

drop if year>1991

*Create instrument and outcome
g good_soil = (ph1_plc_wtm_wtm_0_r>=6.8 & ph1_plc_wtm_wtm_0_r<=7.7)
gen inter_goodtetra=good_soil*tetra_corr
gen inter_badtetra=(1-good_soil)*tetra_corr
g perc_cc =  tot_cc_oripop_corr / (tot_ncc_oripop_corr + tot_cc_oripop_corr)

*Create per capita variables
gen vc_pc=totnpcc_cc_offenses_vc*1000

*Cluster variables
egen cregcodeyear=group(cregcode year)

*Generate total level h.s. for U.S. and its interaction
gen pop_msa=tot_cc_oripop_corr+tot_ncc_oripop_corr
egen pcths_us=wtmean(pcths), by(year) weight(pop_msa)

gen inter_ageent_1939=pcths_us*ageent_1939
gen inter_agelev_1939=pcths_us*agelev_1939
gen inter_ageent_1910=pcths_us*ageent_1910
gen inter_agelev_1910=pcths_us*agelev_1910

*Label variables
label var vc_pc "Violent crime"
label var inter_badtetra "High LB x Lead"
label var inter_ageent_1910 "H.s. US x age school entry"
label var inter_agelev_1910 "H.s. US x age school leave"
label var pcths "Share high school MSA"
label var pcthigh25_cc "Share high school CC"

*Regressions controlling for education
reghdfe vc_pc inter_badtetra inter_ageent_1910   if (year==1960 | year==1970 | year==1980 | year==1990) , absorb(fipsplace_00 year cregcode#year) vce(cluster cregcode#year)
outreg2  using table_control_educ, tex(frag) nocon noni nor2 nonotes label ctitle("",""\"","Violent Crime") addtext(MSA FE, YES, Year FE, YES, C. reg x Year FE, YES, Estimation, OLS, F, .) slow(1000) replace


reghdfe pcthigh25_cc inter_badtetra  inter_ageent_1910, absorb(fipsplace_00 year cregcode#year)  cluster(cregcodeyear)
outreg2 inter_goodtetra inter_ageent_1910  using table_control_educ, tex(frag) nocon noni nor2 nonotes label ctitle("","\ul{  $\hspace{0.2cm}$  Method 1   $\hspace{0.2cm}$  }",""\"""Sh. High School CC") addtext(MSA FE, YES, Year FE, YES, C. reg x Year FE, YES, Estimation, OLS, F, .) slow(1000)

ivreghdfe perc_cc (vc_pc pcthigh25_cc=inter_badtetra inter_ageent_1910 ) , absorb(fipsplace_00 year cregcode#year)  cluster(cregcodeyear)
local ff = trim("`: display %10.2f e(cdf)'")
outreg2 using table_control_educ, tex(frag) nocon noni nor2 nonotes label ctitle("",""\"","Sh. Pop CC") addtext(MSA FE, YES, Year FE, YES, C. reg x Year FE, YES, Estimation, IV, F, `ff') slow(1000) 



reghdfe vc_pc inter_badtetra inter_agelev_1910   if (year==1960 | year==1970 | year==1980 | year==1990) , absorb(fipsplace_00 year cregcode#year) cluster(cregcodeyear)
outreg2 using table_control_educ, tex(frag) nocon noni nor2 nonotes label ctitle("",""\"","Violent Crime") addtext(MSA FE, YES, Year FE, YES, C. reg x Year FE, YES, Estimation, OLS, F, .) sortvar(standardized_vc pcths) slow(1000)

reghdfe pcthigh25_cc inter_badtetra inter_agelev_1910 , absorb(fipsplace_00 year cregcode#year) vce(cluster cregcode#year)
outreg2 using table_control_educ, tex(frag) nocon noni nor2 nonotes label ctitle("","\ul{  $\hspace{0.2cm}$  Method 2   $\hspace{0.2cm}$  }",""\"""Sh. High School CC") addtext(MSA FE, YES, Year FE, YES, C. reg x Year FE, YES, Estimation, OLS, F, .) slow(1000)

ivreghdfe perc_cc (vc_pc pcthigh25_cc = inter_badtetra inter_agelev_1910 ) , absorb(fipsplace_00 year cregcode#year) cluster(cregcodeyear)
local ff = trim("`: display %10.2f e(cdf)'")
outreg2 standardized_vc pcths  using table_control_educ, tex(frag) nocon noni nor2 nonotes label ctitle("",""\"","Sh. Pop CC") addtext(MSA FE, YES, Year FE, YES, C. reg x Year FE, YES, Estimation, IV, F, `ff') slow(1000)


///////////////////////////////////////////////////////////////////////////////////////////
///TABLE A.21
///////////////////////////////////////////////////////////////////////////////////////////
use "finaldata\data_ahs_nat.dta", clear

svyset [pweight=WEIGHT]

gen cc=(METRO==1)

gen crime_o=1 if CRIME==2 & year!=1982 & year!=1983 & year!=1984
replace crime_o=2 if (CRIME==1 | CRIME==3 | CRIME==4 | CRIME==6) & year!=1982 & year!=1983 & year!=1984
replace crime_o=3 if CRIME==5 & year!=1982 & year!=1983 & year!=1984
replace crime_o=. if year==1974

gen crime_probl=(crime_o!=1)
replace crime_probl=. if crime_o==.

gen crime_move=1 if crime_o==3
replace crime_move=0 if crime_o==2

gen school_o=1 if SCH==1
replace school_o=2 if SCH==2 & SCHM==2 & (year!=1978 & year!=1979 & year!=1980)
replace school_o=2 if SCH==2 & (SCHM==1 | SCHM==2 | SCHM==3) & (year==1978 | year==1979 | year==1980)
replace school_o=3 if SCH==2 & SCHM==1 & (year!=1978 & year!=1979 & year!=1980)
replace school_o=3 if SCH==2 & SCHM==4 & (year==1978 | year==1979 | year==1980)
replace school_o=. if year==1974

gen school_probl=(school_o!=1)
replace school_probl=. if school_o==.

gen school_move=1 if school_o==3
replace school_move=0 if school_o==2

gen crime_move_graph=crime_move
replace crime_move_graph=0 if crime_o==1

gen school_move_graph=school_move
replace school_move_graph=0 if school_o==1

file open myfile using table_sumstat_ahs.txt, write replace
file write myfile "\begin{tabular}{l cc cc} \hline\hline \textbf{Variable} & \multicolumn{2}{c}{\textbf{NCC}} & \multicolumn{2}{c}{\textbf{CC}} \\ \cline{2-5} \\ & \textbf{Mean} &  \textbf{s.e.} & \textbf{Mean} & \textbf{s.e.}   \\  \hline" _n

local varlist  "crime_probl school_probl crime_move_graph school_move_graph"
foreach var in `varlist'{

 
if "`var'"=="crime_probl"{
 local name = "Prop. people considering neigh. crime probl."
 }
     else if "`var'"=="school_probl"{
 local name = "Prop. people considering neigh. schools probl."
 }
    else if "`var'"=="crime_move_graph"{
 local name = "Prop. people considering neigh. crime probl such that want to move"
 }
   else if "`var'"=="school_move_graph"{
 local name = "Prop. people considering neigh. schools probl such that want to move"
 }
 else {
 local name = "ERROR"
 }

 foreach i in 0 1{
 svy: mean `var' if cc==`i'
 matrix define bbb = e(b)
 local mmmean_`i' = bbb[1,1]
 local mmean_`i'=string(round(`mmmean_`i'',.01) , "%9.2f")
 matrix define sss = e(V)
 local sssd_`i' = sss[1,1]^0.5
 local ssd_`i'=string(round(`sssd_`i'',.0001) , "%9.4f")
 }
 
  file write myfile ("`name'") _tab "&" %8.2f (`mmean_0') _tab "&"   _tab %8.2f ("`ssd_0'") _tab "&" _tab %8.2f ("`mmean_1'") _tab "&" _tab %8.2f ("`ssd_1'") _tab "\\" _n 
 
 } 
*

file write myfile "\hline \end{tabular}"
file close myfile

///////////////////////////////////////////////////////////////////////////////////////////
///TABLE A.22
///////////////////////////////////////////////////////////////////////////////////////////
use "finaldata\final_cc_data.dta", clear
drop if fipsplace_00==.
xtset fipsplace_00 year

drop if year>1991

*Create instrument and outcome
g good_soil = (ph1_plc_wtm_wtm_0_r>=6.8 & ph1_plc_wtm_wtm_0_r<=7.7)
g bad_soil = 1-good_soil
gen inter_goodtetra=good_soil*tetra_corr
gen inter_badtetra=(1-good_soil)*tetra_corr
gen vc_pc=totnpcc_cc_offenses_vc*1000
egen cdivcodeyear=group(cdivcode year)

gen tetra_corr_19=tetra_corr
forvalues i=1/15{
local j=19+`i'
local l=19-`i'
gen tetra_corr_`j'=L`i'.tetra_corr
gen tetra_corr_`l'=F`i'.tetra_corr
}


keep inter_badtetra bad_soil vc_pc tetra_corr_* FMSA year cdivcodeyear cdivcode fipsplace_00
rename FMSA SMSA

merge 1:m SMSA year using "finaldata\data_ahs.dta"
drop if _merge!=3
drop _merge

gen cc=(METRO==1)

gen inter_badcc=inter_badtetra*cc

label var inter_badtetra "High LB x Lead"
label var cc "Live in CC"
label var inter_badcc "High LB x Lead x Live in CC"


gen crime_o=1 if CRIME==2 & year!=1982 & year!=1983 & year!=1984
replace crime_o=2 if (CRIME==1 | CRIME==3 | CRIME==4 | CRIME==6) & year!=1982 & year!=1983 & year!=1984
replace crime_o=3 if CRIME==5 & year!=1982 & year!=1983 & year!=1984
replace crime_o=. if year==1974

gen crime_probl=(crime_o!=1)
replace crime_probl=. if crime_o==.

gen crime_move=1 if crime_o==3
replace crime_move=0 if crime_o==2

gen school_o=1 if SCH==1
replace school_o=2 if SCH==2 & SCHM==2 & (year!=1978 & year!=1979 & year!=1980)
replace school_o=2 if SCH==2 & (SCHM==1 | SCHM==2 | SCHM==3) & (year==1978 | year==1979 | year==1980)
replace school_o=3 if SCH==2 & SCHM==1 & (year!=1978 & year!=1979 & year!=1980)
replace school_o=3 if SCH==2 & SCHM==4 & (year==1978 | year==1979 | year==1980)
replace school_o=. if year==1974

gen school_probl=(school_o!=1)
replace school_probl=. if school_o==.

gen school_move=1 if school_o==3
replace school_move=0 if school_o==2

label var crime_move "Crime leave"
label var school_move "Schools leave"

ta fipsplace_00, gen(fips_)
drop fips_1
ta year, gen(year_)
drop year_1 year_2 year_9 year_10 year_11

reghdfe crime_probl cc inter_badtetra inter_badcc [pw=WEIGHT], abs(fipsplace_00 year) cluster(SMSA)
outreg2 using table_ahs_crimeschool_move, tex(frag) nocon noni nor2 nonotes label ctitle("Crime probl.") addtext(MSA FE, YES, Year FE, YES, Sample, All, Estimation, OLS) replace
reghdfe school_probl cc inter_badtetra inter_badcc [pw=WEIGHT], abs(fipsplace_00 year) cluster(SMSA)
outreg2 using table_ahs_crimeschool_move, tex(frag) nocon noni nor2 nonotes label ctitle("Schools probl.") addtext(MSA FE, YES, Year FE, YES, Sample, Sch.-age sons, Estimation, OLS) 

reghdfe crime_move cc inter_badtetra inter_badcc [pw=WEIGHT], abs(fipsplace_00 year) cluster(SMSA)
outreg2 using table_ahs_crimeschool_move, tex(frag) nocon noni nor2 nonotes label ctitle("Crime leave") addtext(MSA FE, YES, Year FE, YES, Sample, w/ cr. probl., Estimation, OLS)
reghdfe school_move cc inter_badtetra inter_badcc [pw=WEIGHT], abs(fipsplace_00 year) cluster(SMSA)
outreg2 using table_ahs_crimeschool_move, tex(frag) nocon noni nor2 nonotes label ctitle("Schools leave") addtext(MSA FE, YES, Year FE, YES, Sample, w/ sch. probl., Estimation, OLS)

medeff (regress school_move cc inter_badtetra inter_badcc fips_* year_*) (regress crime_move cc inter_badtetra inter_badcc school_move fips_* year_*) [pw=WEIGHT], mediate(school_move) treat(inter_badcc) sims(300) vce(robust)
local amede_eff=string(round(`r(delta0)',.01) , "%9.2f")
local amede_cil=string(round(`r(delta0lo)',.01) , "%9.2f")
local amede_cih=string(round(`r(delta0hi)',.01) , "%9.2f")
local adire_eff=string(round(`r(zeta0)',.01) , "%9.2f")
local adire_cil=string(round(`r(zeta0lo)',.01) , "%9.2f")
local adire_cih=string(round(`r(zeta0hi)',.01) , "%9.2f")
local atote_eff=string(round(`r(tau)',.01) , "%9.2f")
local atote_cil=string(round(`r(taulo)',.01) , "%9.2f")
local atote_cih=string(round(`r(tauhi)',.01) , "%9.2f")
reghdfe crime_move cc inter_badtetra inter_badcc school_move [pw=WEIGHT], abs(fipsplace_00 year) cluster(SMSA)
outreg2 using table_ahs_crimeschool_move, tex(frag) nocon noni nor2 nonotes label ctitle("Crime leave") addtext(MSA FE, YES, Year FE, YES, Sample, w/ cr. \& sch. probl., Estimation, OLS, Av. Mediation Eff., `amede_eff', Av. Med. Eff. 95% CI, \left[`amede_cil';`amede_cih'\right], Av. Direct Eff., `adire_eff' , Av. Dir. Eff. 95% CI, \left[`adire_cil';`adire_cih'\right], Av. Total Eff., `atote_eff', Av. Tot. Eff. 95% CI, \left[`atote_cil';`atote_cih'\right])

medeff (regress crime_move cc inter_badtetra inter_badcc fips_* year_*) (regress school_move cc inter_badtetra inter_badcc crime_move fips_* year_*) [pw=WEIGHT], mediate(crime_move) treat(inter_badcc) sims(300) vce(robust)
local amede_eff=string(round(`r(delta0)',.01) , "%9.2f")
local amede_cil=string(round(`r(delta0lo)',.01) , "%9.2f")
local amede_cih=string(round(`r(delta0hi)',.01) , "%9.2f")
local adire_eff=string(round(`r(zeta0)',.01) , "%9.2f")
local adire_cil=string(round(`r(zeta0lo)',.01) , "%9.2f")
local adire_cih=string(round(`r(zeta0hi)',.01) , "%9.2f")
local atote_eff=string(round(`r(tau)',.01) , "%9.2f")
local atote_cil=string(round(`r(taulo)',.01) , "%9.2f")
local atote_cih=string(round(`r(tauhi)',.01) , "%9.2f")
reghdfe school_move cc inter_badtetra inter_badcc crime_move [pw=WEIGHT], abs(fipsplace_00 year) cluster(SMSA)
outreg2 using table_ahs_crimeschool_move, tex(frag) nocon noni nor2 nonotes label ctitle("School leave") addtext(MSA FE, YES, Year FE, YES, Sample, w/ cr. \& sch. probl., Estimation, OLS, Av. Mediation Eff., `amede_eff', Av. Med. Eff. 95% CI, \left[`amede_cil';`amede_cih'\right], Av. Direct Eff., `adire_eff' , Av. Dir. Eff. 95% CI, \left[`adire_cil';`adire_cih'\right], Av. Total Eff., `atote_eff', Av. Tot. Eff. 95% CI, \left[`atote_cil';`atote_cih'\right])


///////////////////////////////////////////////////////////////////////////////////////////
///TABLE A.23
///////////////////////////////////////////////////////////////////////////////////////////
clear
set more off
set matsize 800

set mem 80m

** Create temp file with state-level hwy data for instrument **

use "finaldata\hwy-allyr-state.dta"

collapse (sum) lenc, by(year)

sort year

replace year=1900+year

gen sshrc = lenc/lenc[_N]
sort year

tempfile _temp
save `_temp'


use "finaldata\final_cc_data.dta", clear

sort year
merge m:1 year using `_temp'
tab _merge


drop if year>1991


g good_soil = (ph1_plc_wtm_wtm_0_r>=6.8 & ph1_plc_wtm_wtm_0_r<=7.7)
g good_soil_ncc = (ph_ncc>=6.8 & ph_ncc<=7.7)

*Cluster variables
egen cdivcodeyear=group(cdivcode year)

*Generate 1960 variables
foreach var of varlist cpctblk pctblk{
gen `var'_1960_2=`var' if year==1960
egen `var'_1960=max(`var'_1960_2), by(FMSA)
drop `var'_1960_2
}
gen prop_black_1960=cpctblk_1960/pctblk_1960
g area_plc_sqkm=area_plc/1000000
g area_ncc_sqkm=area_ncc/1000000
gen pop_cc_1960_2=tot_cc_oripop_corr if year==1960
egen pop_cc_1960=max(pop_cc_1960_2), by(FMSA)
gen dens_cc_1960=pop_cc_1960/area_plc_sqkm
g dens_cc=tot_cc_oripop_corr/area_plc_sqkm
g dens_ncc=tot_ncc_oripop_corr/area_ncc_sqkm
egen rays_planc_1947=max(rays_planc), by(fipsplace_00)




expand 2, gen(dup)

gen cc=(dup==1)

gen vc= totnpcc_ncc_offenses_vc*1000 if dup==0
replace vc=totnpcc_cc_offenses_vc*1000 if dup==1

gen id=fipsplace_00
replace id=fipsplace_00*1000  if dup==0

g highLB = (1-good_soil)  if dup==1
replace highLB = (1-good_soil_ncc) if dup==0

g ohighLB = (1-good_soil)  if dup==0
replace ohighLB = (1-good_soil_ncc) if dup==1


su tetra_corr, detail
g lead_n = (tetra_corr - r(min))/(1-r(min))

g leadXcc = cc * lead_n
g highLBXcc = cc * highLB
g highLBXlead = lead_n * highLB 
g ohighLBXlead = lead_n * ohighLB 

g leadXccXhighLB = lead_n * highLB * cc



label var cc "CC"
label var highLBXlead "High LB x Lead"
label var leadXccXhighLB "High LB x Lead x CC"
label var leadXcc "Lead x CC"


g inter_badtetra = (1-good_soil) * tetra_corr

*Create heterogeneities
gen inter_rays_planc=inter_badtetra*rays_planc
gen inter_rays_planc_1947=inter_badtetra*rays_planc_1947
gen inter_cpctblk_1960=inter_badtetra*cpctblk_1960
gen inter_prop_black_1960=inter_badtetra*prop_black_1960
g ldens = ln(dens_cc_1960)
su ldens if cc==1, detail
gen ldens_st = (ldens - r(mean))/r(sd)
gen ldens_p5 = ldens - r(p5)
gen inter_dens_cc_1960_st=inter_badtetra*ldens_st
gen inter_dens_cc_1960_p5=inter_badtetra*ldens_p5
gen inter_dens_cc_1960=inter_badtetra*ldens

*Label variables
label var inter_badtetra "High LB x Lead"
label var inter_cpctblk_1960 "High LB x Lead x Blacks CC 1960"
label var inter_prop_black_1960 "High LB x Lead x Blacks CC / MSA 1960"
label var inter_rays_planc "High LB x Lead x Planned highways CC"
label var inter_rays_planc_1947 "High LB x Lead x Planned highways CC"
label var inter_dens_cc_1960 "High LB x Lead x ln(Density CC 1960)"
label var inter_dens_cc_1960_p5 "High LB x Lead x ln(Density CC 1960)"
label var inter_dens_cc_1960_st "High LB x Lead x ln(Density CC 1960)"


reghdfe vc cc leadXcc highLBXlead leadXccXhighLB , absorb(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear fipsplace_00)
outreg2 using table_vc_ccncc,  tex(frag) nocon noni nor2 nonotes label ctitle("Violent crime") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS) replace

g leadXhighway = tetra_corr * rays_planc_1947
g leadXdens = tetra_corr * ldens
g leadXdens_p5 = tetra_corr * ldens_p5
g leadXdens_st = tetra_corr * ldens_st
label var leadXhighway  "Lead x Planned highways CC"
label var leadXdens_st "Lead x ln(Density CC 1960)"
label var leadXdens_p5 "Lead x ln(Density CC 1960)"
label var leadXdens "Lead x ln(Density CC 1960)"



reghdfe vc highLBXlead leadXhighway inter_rays_planc_1947 if cc==1, abs(fipsplace_00 year cdivcode#year) vce(cluster cdivcode#year)
outreg2 using table_vc_ccncc, tex(frag) nocon noni nor2 nonotes label ctitle("Violent crime") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS)

reghdfe vc highLBXlead leadXdens_p5 inter_dens_cc_1960_p5  if cc==1, abs(fipsplace_00 year cdivcode#year) vce(cluster cdivcode#year)
outreg2 using table_vc_ccncc, tex(frag) nocon noni nor2 nonotes label ctitle("Violent crime") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS)





///////////////////////////////////////////////////////////////////////////////////////////
///TABLE A.24 & FIGURE A.18/////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////
use "finaldata\final_cc_data.dta", clear
drop if fipsplace_00==.
xtset fipsplace_00 year

drop if year>1991

*Create instrument and outcome
g good_soil = (ph1_plc_wtm_wtm_0_r>=6.8 & ph1_plc_wtm_wtm_0_r<=7.7)
gen inter_goodtetra=good_soil*tetra_corr
gen inter_badtetra=(1-good_soil)*tetra_corr
g perc_cc =  tot_cc_oripop_corr / (tot_ncc_oripop_corr + tot_cc_oripop_corr)

*Create standardized variables
egen sd_vc=sd(totnpcc_cc_offenses_vc)
egen sd_pop_cc=sd(tot_cc_oripop_corr)
egen sd_pop_ncc=sd(tot_ncc_oripop_corr)
gen vc_pc=totnpcc_cc_offenses_vc * 1000
gen vc_ncc_pc=totnpcc_ncc_offenses_vc * 1000

*Create aggregate variables
egen vcn_cc_us=total( totn_cc_offenses_vc),by(year)
egen vcn_ncc_us=total( totn_ncc_offenses_vc),by(year)
egen pop_cc_us=total(tot_cc_oripop_corr),by(year)
egen pop_ncc_us=total(tot_ncc_oripop_corr),by(year)
gen vcnpcc_cc_us=vcn_cc_us/pop_cc_us
gen vcnpcc_ncc_us=vcn_ncc_us/pop_ncc_us
gen perc_cc_us=pop_cc_us/(pop_cc_us+pop_ncc_us)

*Generate other variables
gen pop_msa=tot_cc_oripop_corr+tot_ncc_oripop_corr
gen pop_cc_1960_2=tot_cc_oripop_corr if year==1960
egen pop_cc_1960=max(pop_cc_1960_2) ,by(FMSA)
foreach var of varlist pctblk empman pcths pubtrans house{
gen `var'_1960_2=`var' if year==1960
egen `var'_1960=max(`var'_1960_2) ,by(FMSA)
gen inter_vc`var'_1960=totnpcc_cc_offenses_vc*`var'_1960
}
foreach var of varlist nchd_land cong{
gen `var'_1980_2=`var' if year==1980
egen `var'_1980=max(`var'_1980_2) ,by(FMSA)
gen inter_vc`var'_1980=totnpcc_cc_offenses_vc*`var'_1980
}
xi: reg vc_pc inter_goodtetra i.year i.fipsplace_00, robust
predict resid, resid
gen inter_vcpop_1960=totnpcc_cc_offenses_vc*pop_cc_1960
gen placeyear=fipsplace_00*year
gen stateyear=STATEFP00*year
gen ph_msa=(ph1_plc_wtm_wtm_0_r*area_plc+ph_ncc*area_ncc)/(area_plc+area_ncc)
g good_soil_ncc = (ph_ncc>=6.8 & ph_ncc<=7.7)
gen inter_goodtetra_ncc=good_soil_ncc*tetra_corr
gen inter_badtetra_ncc=(1-good_soil_ncc)*tetra_corr
g good_soil_msa = (ph_msa>=6.8 & ph_msa<=7.7)
gen inter_goodtetra_msa=good_soil_msa*tetra_corr
gen pctover25=over25/pop_msa
gen prop_income=cincome/income
gen prop_black= cpctblk / pctblk
gen prop_emp= cemp/emp

*Label variables
label var vc_pc "Standardized vc p.c."
label var inter_goodtetra "Low LB x Lead"
label var vcnpcc_cc_us "Violent crime CC, US"
label var vcnpcc_ncc_us "Violent crime NCC, US"
label var resid "Residuals first stage"
label var inter_vcpop_1960 "Vc pc x Population in 1960 in CC"
label var pctblk "% Blacks MSA"
label var income "Median family income"
label var pctover25 "% people over 25"
label var pct65up "% people over 65"

*Spillover of crime to CC or NCC
label var ph_ncc "pH NCC"
label var inter_goodtetra "Low LB CC x Lead"
label var inter_goodtetra_ncc "Low LB NCC x Lead"
label var inter_goodtetra_msa "Low LB MSA x Lead"

*Crime effect different wrt density
g area_plc_sqkm=area_plc/1000000
g area_ncc_sqkm=area_ncc/1000000
g dens_cc=tot_cc_oripop_corr/area_plc_sqkm
g dens_ncc=tot_ncc_oripop_corr/area_ncc_sqkm
gen pop_ncc_1960_2=tot_ncc_oripop_corr if year==1960
egen pop_ncc_1960=max(pop_ncc_1960_2), by(FMSA)
gen dens_cc_1960=pop_cc_1960/area_plc_sqkm
gen dens_ncc_1960=pop_ncc_1960/area_ncc_sqkm
gen inter_instrumdens_cc=inter_goodtetra*dens_cc_1960
gen inter_instrumdens_ncc=inter_goodtetra*dens_ncc_1960
gen inter_tetradens_cc=tetra_corr*dens_cc_1960
gen inter_tetradens_ncc=tetra_corr*dens_ncc_1960
label var inter_goodtetra "Low LB CC x Lead"
label var inter_goodtetra_ncc "Low LB NCC x Lead"
label var inter_badtetra "High LB CC x Lead"
label var inter_badtetra_ncc "High LB NCC x Lead"
label var inter_tetradens_cc "Lead x Density CC 1960"
label var inter_instrumdens_cc "Low LB CC x Lead x Density CC 1960"
label var inter_tetradens_ncc "Lead x Density NCC 1960"
label var inter_instrumdens_ncc "Low LB NCC x Lead x Density NCC 1960"

reg ph1_plc_wtm_wtm_0_r ph_ncc if year==1960, robust
outreg2  using table_rob_spillover_ncc, keep(ph_ncc) tex(frag) nocon noni nonotes label ctitle("pH CC") addtext(MSA FE, NO, Year FE, NO, C. div x Year, NO, Estimation, OLS, s.e. cluster, NO) slow(10000) replace
reghdfe vc_ncc_pc inter_badtetra_ncc, abs(fipsplace_00 year cdivcode#year) vce(conventional)
outreg2  using table_rob_spillover_ncc, keep(inter_badtetra_ncc) tex(frag) nocon noni nor2 nonotes label ctitle("Violent crime NCC") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS, s.e. cluster, NO) slow(10000)
reghdfe vc_ncc_pc inter_badtetra_ncc, abs(fipsplace_00 year cdivcode#year) cluster(FMSA)
outreg2  using table_rob_spillover_ncc, keep(inter_badtetra_ncc) tex(frag) nocon noni nor2 nonotes label ctitle("Violent crime NCC") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS, s.e. cluster, MSA) slow(10000)
reghdfe vc_ncc_pc inter_badtetra inter_badtetra_ncc, abs(fipsplace_00 year cdivcode#year) vce(conventional)
outreg2  using table_rob_spillover_ncc, keep(inter_badtetra inter_badtetra_ncc) tex(frag) nocon noni nor2 nonotes label ctitle("Violent crime NCC") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS, s.e. cluster, NO) slow(10000)
reghdfe vc_ncc_pc inter_badtetra inter_badtetra_ncc, abs(fipsplace_00 year cdivcode#year) cluster(FMSA)
outreg2  using table_rob_spillover_ncc, keep(inter_badtetra inter_badtetra_ncc) tex(frag) nocon noni nor2 nonotes label ctitle("Violent crime NCC") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS, s.e. cluster, MSA) slow(10000)


*Marginal plot first stage interacted with density (done between pctile 20 and 80)
sum dens_cc_1960
label var dens_cc_1960 "Population density in CC in 1960"
xtreg vc_pc i.year i.cdivcode#i.year inter_badtetra c.inter_badtetra#c.dens_cc_1960 c.inter_badtetra#c.dens_cc_1960#c.dens_cc_1960, fe vce(conventional)
test c.inter_badtetra#c.dens_cc_1960 c.inter_badtetra#c.dens_cc_1960#c.dens_cc_1960
local pval = trim("`: display %10.2f r(p)'")
margins , at(dens_cc_1960=(0(1000)10500)) dydx(inter_badtetra)
marginsplot, title(Eff. Lead x High LB on violent crime) caption(p-value joint significance polynomials: `pval')
graph export margin_first_cc_dens.png, as(png) replace




///////////////////////////////////////////////////////////////////////////////////////////
///Table A.25 - Table A.26/////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////
clear
set more off
set matsize 800

set mem 80m

** Create temp file with state-level hwy data for instrument **
use "finaldata\hwy-allyr-state.dta"

collapse (sum) lenc, by(year)

sort year

replace year=1900+year

gen sshrc = lenc/lenc[_N]
sort year

tempfile _temp
save `_temp'


*** Add on instrument to MSA - level data *********** 
use "finaldata\final_cc_data.dta", clear
*drop _merge

sort year
merge m:1 year using `_temp' 
tab _merge
** Should be all 3
drop _merge

drop if fipsplace_00==.
xtset fipsplace_00 year

drop if year>1991

*Create instrument and outcome
g good_soil = (ph1_plc_wtm_wtm_0_r>=6.8 & ph1_plc_wtm_wtm_0_r<=7.7)
g bad_soil=1-good_soil
gen inter_goodtetra=good_soil*tetra_corr
gen inter_badtetra=(1-good_soil)*tetra_corr
g perc_cc =  tot_cc_oripop_corr / (tot_ncc_oripop_corr + tot_cc_oripop_corr)
gen vc_pc=totnpcc_cc_offenses_vc*1000


*Label variables
label var vc_pc "Violent crime"
label var inter_goodtetra "Low LB x Lead"
label var inter_badtetra "High LB x Lead"


*Baum-Snow variables
gen raysxplan = sshrc*rays_planc
replace pop = pop/1000000
replace lagpop = lagpop/1000000
replace cpop2 = cpop2/1000000
gen lpop = log(pop)
gen lcpop = log(cpop)
gen lcpop2 = log(cpop2)

gen POP50 = pop if year==50
egen pop50 = max(POP50), by(FMSA)
gen CPOP50 = cpop2 if year==50
egen cpop50 = max(CPOP50), by(FMSA)

label var racc "Rays"
label var rays_planc "Rays in plan"
label var raysxplan "Rays in plan x Prop. hgw"
label var good_soil "Low LB"
label var bad_soil "High LB"
*FBL: in paper  BS is other definition instrument (rays in the plan) x (MSA mileage of highways running through thecentral city at time t)/(MSA mileage of highways running through the central city in 1990).

*Bias analysis
reghdfe inter_badtetra raysxplan i.year if pop50>.1 & cpop50>.05, absorb(fipsplace_00) vce(cluster FMSA)
outreg2  using table_rob_highway_bias_gt, keep(raysxplan) tex(frag) nocon noni nor2 nonotes label ctitle("High LB x Lead") addtext(MSA FE, YES, Year FE, YES, Estimation, OLS) replace
reghdfe inter_badtetra racc i.year if pop50>.1 & cpop50>.05, absorb(fipsplace_00) vce(cluster FMSA)
outreg2  using table_rob_highway_bias_gt, keep(racc) tex(frag) nocon noni nor2 nonotes label ctitle("High LB x Lead") addtext(MSA FE, YES, Year FE, YES, Estimation, OLS)
reg rays_planc bad_soil if year==1960 & pop50>.1 & cpop50>.05, cluster(FMSA)
outreg2  using table_rob_highway_bias_gt, keep(bad_soil) tex(frag) nocon noni nor2 nonotes label ctitle("Rays in plan") addtext(MSA FE, NO, Year FE, NO, Estimation, OLS)
reg racc bad_soil i.year if pop50>.1 & cpop50>.05, cluster(FMSA)
outreg2  using table_rob_highway_bias_gt, keep(bad_soil) tex(frag) nocon noni nor2 nonotes label ctitle("Rays") addtext(MSA FE, NO, Year FE, YES, Estimation, OLS)



*Regressions
ivreghdfe perc_cc i.year (racc= raysxplan) if pop50>.1 & cpop50>.05, absorb(fipsplace_00) cluster(FMSA)
local ff = trim("`: display %10.2f e(cdf)'")
outreg2 using table_rob_highway, keep(racc) tex(frag) nocon noni nor2 nonotes label ctitle("Sh pop CC") addtext(MSA FE, YES, Year FE, YES, Estimation, IV, F, `ff') replace
reghdfe racc i.year raysxplan if pop50>.1 & cpop50>.05, absorb(fipsplace_00) vce(cluster FMSA)
outreg2  using table_rob_highway, keep(raysxplan) tex(frag) nocon noni nor2 nonotes label ctitle("Rays") addtext(MSA FE, YES, Year FE, YES, Estimation, OLS, F, .)
ivreghdfe perc_cc i.year (vc_pc = inter_badtetra ) if (year==1960 | year==1970 | year==1980 | year==1990) & pop50>.1 & cpop50>.05, absorb(fipsplace_00) cluster(FMSA)
local ff = trim("`: display %10.2f e(cdf)'")
outreg2  using table_rob_highway, keep(vc_pc) tex(frag) nocon noni nor2 nonotes label ctitle("Sh pop CC") addtext(MSA FE, YES, Year FE, YES, Estimation, IV, F, `ff')
reghdfe vc_pc i.year inter_badtetra if (year==1960 | year==1970 | year==1980 | year==1990) & pop50>.1 & cpop50>.05, absorb(fipsplace_00) vce(cluster FMSA)
outreg2  using table_rob_highway, keep(inter_badtetra) tex(frag) nocon noni nor2 nonotes label ctitle("Violent crime") addtext(MSA FE, YES, Year FE, YES, Estimation, OLS, F, .)
ivreghdfe perc_cc i.year (vc_pc racc= inter_badtetra raysxplan) if pop50>.1 & cpop50>.05, absorb(fipsplace_00) cluster(FMSA)
local ff = trim("`: display %10.2f e(cdf)'")
outreg2  using table_rob_highway, keep(racc vc_pc) tex(frag) nocon noni nor2 nonotes label ctitle("Sh pop CC") addtext(MSA FE, YES, Year FE, YES, Estimation, IV, F, `ff')
reghdfe racc i.year raysxplan inter_badtetra if pop50>.1 & cpop50>.05, absorb(fipsplace_00) vce(cluster FMSA)
outreg2  using table_rob_highway, keep(raysxplan inter_badtetra) tex(frag) nocon noni nor2 nonotes label ctitle("Rays") addtext(MSA FE, YES, Year FE, YES, Estimation, OLS, F, .)
reghdfe vc_pc i.year inter_badtetra raysxplan  if (year==1960 | year==1970 | year==1980 | year==1990) & pop50>.1 & cpop50>.05, absorb(fipsplace_00) vce(cluster FMSA)
outreg2  using table_rob_highway, keep(raysxplan inter_badtetra) tex(frag) nocon noni nor2 nonotes label ctitle("Violent crime") addtext(MSA FE, YES, Year FE, YES, Estimation, OLS, F, .)






///////////////////////////////////////////////////////////////////////////////////////////
///Table A.27 & FIGURE A.19///////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////
use "finaldata\final_cc_data.dta", clear

g treat = (ph1_plc_wtm_wtm_0_r>=6.8 & ph1_plc_wtm_wtm_0_r<=7.7)

drop if fipsplace_00==.
xtset fipsplace_00 year 

label var treat "pH 6.8-7.7"
label var ph1_plc_wtm_wtm_0_r "pH"

*Regressions
reg  corn_pot treat i.cdivcode if year==1990, robust
outreg2  using table_rob_agroprod, keep(treat) tex(frag) nocon noni nonotes label ctitle("Corn") addtext(C. div FE, YES, Estimation, OLS) replace
reg  soy_pot treat i.cdivcode if year==1990, robust
outreg2 treat using table_rob_agroprod, keep(treat) tex(frag) nocon noni nonotes label ctitle("Soy") addtext(C. div FE, YES, Estimation, OLS)
reg  wheat_pot treat i.cdivcode if year==1990, robust
outreg2 treat using table_rob_agroprod, keep(treat) tex(frag) nocon noni nonotes label ctitle("Wheat") addtext(C. div FE, YES, Estimation, OLS)
reg  alfa_pot treat i.cdivcode if year==1990, robust
outreg2 treat using table_rob_agroprod, keep(treat) tex(frag) nocon noni nonotes label ctitle("Alfalfa") addtext(C. div FE, YES, Estimation, OLS)
reg  cott_pot treat i.cdivcode if year==1990, robust
outreg2 treat using table_rob_agroprod, keep(treat) tex(frag) nocon noni nonotes label ctitle("Cotton") addtext(C. div FE, YES, Estimation, OLS)

*Margins
xi: reg corn_pot c.ph1_plc_wtm_wtm_0_r##c.ph1_plc_wtm_wtm_0_r##c.ph1_plc_wtm_wtm_0_r##c.ph1_plc_wtm_wtm_0_r i.cdivcode if year==1990, robust
margins, at(ph1_plc_wtm_wtm_0_r = (5(0.2)8)) dydx(ph1_plc_wtm_wtm_0_r)
marginsplot, title(Corn) xline(6.8, lpattern(dash) lcolor(red)) xline(7.7, lpattern(dash) lcolor(red)) xlabel(5 5.5 6 6.5 7 7.5 8) saving(corn, replace) 

xi: reg soy_pot c.ph1_plc_wtm_wtm_0_r##c.ph1_plc_wtm_wtm_0_r##c.ph1_plc_wtm_wtm_0_r##c.ph1_plc_wtm_wtm_0_r i.cdivcode if year==1990, robust
margins, at(ph1_plc_wtm_wtm_0_r = (5(0.2)8)) dydx(ph1_plc_wtm_wtm_0_r)
marginsplot, title(Soy) xline(6.8, lpattern(dash) lcolor(red)) xline(7.7, lpattern(dash) lcolor(red)) xlabel(5 5.5 6 6.5 7 7.5 8) saving(soy, replace) 

xi: reg wheat_pot c.ph1_plc_wtm_wtm_0_r##c.ph1_plc_wtm_wtm_0_r##c.ph1_plc_wtm_wtm_0_r##c.ph1_plc_wtm_wtm_0_r i.cdivcode if year==1990, robust
margins, at(ph1_plc_wtm_wtm_0_r = (5(0.2)8)) dydx(ph1_plc_wtm_wtm_0_r)
marginsplot, title(Wheat) xline(6.8, lpattern(dash) lcolor(red)) xline(7.7, lpattern(dash) lcolor(red)) xlabel(5 5.5 6 6.5 7 7.5 8) saving(wheat, replace) 
 
xi: reg alfa_pot c.ph1_plc_wtm_wtm_0_r##c.ph1_plc_wtm_wtm_0_r##c.ph1_plc_wtm_wtm_0_r##c.ph1_plc_wtm_wtm_0_r i.cdivcode if year==1990, robust
margins, at(ph1_plc_wtm_wtm_0_r = (5(0.2)8)) dydx(ph1_plc_wtm_wtm_0_r)
marginsplot, title(Alfalfa) xline(6.8, lpattern(dash) lcolor(red)) xline(7.7, lpattern(dash) lcolor(red)) xlabel(5 5.5 6 6.5 7 7.5 8) saving(alfa, replace) 

xi: reg cott_pot c.ph1_plc_wtm_wtm_0_r##c.ph1_plc_wtm_wtm_0_r##c.ph1_plc_wtm_wtm_0_r##c.ph1_plc_wtm_wtm_0_r i.cdivcode if year==1990, robust
margins, at(ph1_plc_wtm_wtm_0_r = (5(0.2)8)) dydx(ph1_plc_wtm_wtm_0_r)
marginsplot, title(Cotton) xline(6.8, lpattern(dash) lcolor(red)) xline(7.7, lpattern(dash) lcolor(red)) xlabel(5 5.5 6 6.5 7 7.5 8) saving(cott, replace) 
 
 
 
 
///////////////////////////////////////////////////////////////////////////////////////////
///Table A.28////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////
use "finaldata\final_cc_data.dta", clear

set matsize  700
drop if fipsplace_00==.
xtset fipsplace_00 year

drop if year>=1991

***Generate variables
egen sd_vc=sd(totnpcc_cc_offenses_vc)
gen vc_pc=totnpcc_cc_offenses_vc * 1000

g good_soil = (ph1_plc_wtm_wtm_0_r>=6.8 & ph1_plc_wtm_wtm_0_r<=7.7)
g good_soil_m=(ph1_plc_wtm_wtm_m_r>=6.8 & ph1_plc_wtm_wtm_m_r<=7.7)
gen inter_goodtetra=good_soil*tetra_corr
gen inter_badtetra=(1-good_soil)*tetra_corr
gen inter_goodtetra_m=good_soil_m*tetra_corr
gen inter_badtetra_m=(1-good_soil_m)*tetra_corr

***Label variables
label var inter_goodtetra "Low LB surface x Lead"
label var inter_badtetra "High LB surface x Lead"
label var inter_goodtetra_m "Low LB layers x Lead"
label var inter_badtetra_m "High LB layers x Lead"

***Regression
reghdfe vc_pc inter_badtetra , abs(fipsplace_00 year cdivcode#year) vce(cluster cdivcode#year)
test _b[inter_badtetra]=0
local ff = trim("`: display %10.2f r(F)'")
outreg2 inter_badtetra using table_fs_soilsurface, tex(frag) nocon noni nonotes label ctitle("Violent crime") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS, F, `ff') replace
reghdfe vc_pc inter_badtetra inter_badtetra_m, abs(fipsplace_00 year cdivcode#year) vce(cluster cdivcode#year)
test _b[inter_badtetra]=0
local ff = trim("`: display %10.2f r(F)'")
outreg2 inter_badtetra inter_badtetra_m using table_fs_soilsurface, tex(frag) nocon noni nonotes label ctitle("Violent crime") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS, F, `ff')





///////////////////////////////////////////////////////////////////////////////////////////
///Table A.29 - A.30 - A.32/////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////
use "finaldata\final_cc_data.dta", clear
drop if fipsplace_00==.
xtset fipsplace_00 year

drop if year>1991

*Create instrument and outcome
g good_soil = (ph1_plc_wtm_wtm_0_r>=6.8 & ph1_plc_wtm_wtm_0_r<=7.7)
gen inter_goodtetra=good_soil*tetra_corr
gen inter_badtetra=(1-good_soil)*tetra_corr
g perc_cc =  tot_cc_oripop_corr / (tot_ncc_oripop_corr + tot_cc_oripop_corr)
g vc_pc = totnpcc_cc_offenses_vc * 1000
gen nvc_pc = (totnpcc_cc_offenses_17c + totnpcc_cc_offenses_21c + totnpcc_cc_offenses_22c)*1000
g vc_pc_1c = totnpcc_cc_offenses_1c*1000
g vc_pc_3c = totnpcc_cc_offenses_3c*1000
g vc_pc_6c = totnpcc_cc_offenses_6c*1000
g vc_pc_11c = totnpcc_cc_offenses_11c*1000
g vc_pc_17c = totnpcc_cc_offenses_17c*1000
g vc_pc_21c = totnpcc_cc_offenses_21c*1000
g vc_pc_22c = totnpcc_cc_offenses_22c*1000

label var totnpcc_cc_offenses_1c "Murder"
label var totnpcc_cc_offenses_2c "Manslaughter"
label var totnpcc_cc_offenses_3c "Rape"
label var totnpcc_cc_offenses_4c "(Forcible)"
label var totnpcc_cc_offenses_5c "(Attempted)"
label var totnpcc_cc_offenses_6c "Robbery"
label var totnpcc_cc_offenses_7c "(Gun)"
label var totnpcc_cc_offenses_8c "(Knife)"
label var totnpcc_cc_offenses_9c "(Other weapon)"
label var totnpcc_cc_offenses_10c "(Strong arm)"
label var totnpcc_cc_offenses_11c "Assaults"
label var totnpcc_cc_offenses_12c "(Gun)"
label var totnpcc_cc_offenses_13c "(Knife)"
label var totnpcc_cc_offenses_14c "(Other weapon)"
label var totnpcc_cc_offenses_15c "(Hand-feet)"
label var totnpcc_cc_offenses_16c "(Simple)"
label var totnpcc_cc_offenses_17c "Burglary"
label var totnpcc_cc_offenses_18c "(Force entry)"
label var totnpcc_cc_offenses_19c "(Entry no force)"
label var totnpcc_cc_offenses_20c "(Attempted)"
label var totnpcc_cc_offenses_21c "Larceny"
label var totnpcc_cc_offenses_22c "Vehicle theft"
label var totnpcc_cc_offenses_23c "(Auto theft)"
label var totnpcc_cc_offenses_24c "(Truck-bus theft)"
label var totnpcc_cc_offenses_25c "(Other vehicles)"
label var totnpcc_cc_offenses_26c "Total"
label var totnpcc_cc_offenses_vc "Violent"
label var totnpcc_cc_offenses_ass "Aggr. assaults"

label var inter_badtetra "High LB x Lead"


su vc_pc if year==1960
g NORMvc_pc = vc_pc / r(mean) 
reghdfe NORMvc_pc inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
outreg2 inter_badtetra using table_normeff, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("Violent") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS)  replace
sleep 500

su vc_pc_1c if year==1960
g NORMvc_pc_1c = vc_pc_1c / r(mean) 
reghdfe NORMvc_pc_1c inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
outreg2 inter_badtetra using table_normeff, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("Murder") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS) 
sleep 500

su vc_pc_3c if year==1960
g NORMvc_pc_3c = vc_pc_3c / r(mean)
reghdfe NORMvc_pc_3c inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
outreg2 inter_badtetra using table_normeff, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("Rape") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS)  
sleep 500

su vc_pc_6c if year==1960
g NORMvc_pc_6c = vc_pc_6c / r(mean)
reghdfe NORMvc_pc_6c inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
outreg2 inter_badtetra using table_normeff, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("Robbery") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS)  
sleep 500

su vc_pc_11c if year==1960
g NORMvc_pc_11c = vc_pc_11c / r(mean)
reghdfe NORMvc_pc_11c inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
outreg2 inter_badtetra using table_normeff, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("Assaults") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS)  
sleep 500


su nvc_pc if year==1960
g NORMnvc_pc = nvc_pc / r(mean) 
reghdfe NORMnvc_pc inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
outreg2 inter_badtetra using table_normeff, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("Property") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS) 
sleep 500

su vc_pc_17c if year==1960
g NORMvc_pc_17 = vc_pc_17c / r(mean)
reghdfe NORMvc_pc_17 inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
outreg2 inter_badtetra using table_normeff, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("Burglary") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS) 
sleep 500

su vc_pc_21c if year==1960
g NORMvc_pc_21 = vc_pc_21c / r(mean)
reghdfe NORMvc_pc_21 inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
outreg2 inter_badtetra using table_normeff, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("Larceny") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS) 
sleep 500

su vc_pc_22c if year==1960
g NORMvc_pc_22 = vc_pc_22c / r(mean)
reghdfe NORMvc_pc_22 inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
outreg2 inter_badtetra using table_normeff, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("Motor Veh.") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS) 



************************LN
g lvc = ln(vc_pc)
reghdfe lvc inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
outreg2 inter_badtetra using table_lncrimes, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("ln(Violent)") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS)  replace

g lvc1 = ln(vc_pc_1c)
g lvc3 = ln(vc_pc_3c)
g lvc6 = ln(vc_pc_6c)
g lvc11 = ln(vc_pc_11c)
reghdfe lvc1 inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
outreg2 inter_badtetra using table_lncrimes, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("ln(Murder)") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS)
sleep 500

reghdfe lvc3 inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
outreg2 inter_badtetra using table_lncrimes, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("ln(Rape)") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS)
sleep 500

reghdfe lvc6 inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
outreg2 inter_badtetra using table_lncrimes, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("ln(Robbery)") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS)
sleep 500

reghdfe lvc11 inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
outreg2 inter_badtetra using table_lncrimes, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("ln(Assaults)") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS)
sleep 500

g lnvc = ln(nvc_pc)
reghdfe lnvc inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
outreg2 inter_badtetra using table_lncrimes, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("ln(Property)") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS)  
sleep 500

g lvc17 = ln(vc_pc_17c)
g lvc21 = ln(vc_pc_21c)
g lvc22 = ln(vc_pc_22c)
reghdfe lvc17 inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
outreg2 inter_badtetra using table_lncrimes, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("ln(Burglary)") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS)  
sleep 500

reghdfe lvc21 inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
outreg2 inter_badtetra using table_lncrimes, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("ln(Larceny)") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS)  
sleep 500

reghdfe lvc22 inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
outreg2 inter_badtetra using table_lncrimes, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("ln(Motor)") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS)  

****************CONTROLLING
reghdfe vc_pc nvc_pc inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
outreg2 inter_badtetra using table_vcnvc, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("Violent") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Control VC, NO, Control NVC, YES, Estimation, OLS) replace
sleep 500

reghdfe vc_pc_1c nvc_pc inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
outreg2 inter_badtetra using table_vcnvc, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("Murder") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Control VC, NO, Control NVC, YES, Estimation, OLS)  
sleep 500

reghdfe vc_pc_3c nvc_pc inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
outreg2 inter_badtetra using table_vcnvc, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("Rape") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Control VC, NO, Control NVC, YES, Estimation, OLS)  
sleep 500

reghdfe vc_pc_6c nvc_pc inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
outreg2 inter_badtetra using table_vcnvc, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("Robbery") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Control VC, NO, Control NVC, YES, Estimation, OLS)  
sleep 500

reghdfe vc_pc_11c nvc_pc inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
outreg2 inter_badtetra using table_vcnvc, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("Assault") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Control VC, NO, Control NVC, YES, Estimation, OLS)  
sleep 500

reghdfe nvc_pc vc_pc inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
outreg2 inter_badtetra using table_vcnvc, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("Property") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Control VC, YES, Control NVC, NO, Estimation, OLS)  
sleep 500

reghdfe vc_pc_17c vc_pc inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
outreg2 inter_badtetra using table_vcnvc, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("Burglary") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Control VC, YES, Control NVC, NO, Estimation, OLS)  
sleep 500

reghdfe vc_pc_21c vc_pc inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
outreg2 inter_badtetra using table_vcnvc, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("Larceny") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Control VC, YES, Control NVC, NO, Estimation, OLS) 
sleep 500

reghdfe vc_pc_22c vc_pc inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
outreg2 inter_badtetra using table_vcnvc, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("Motor Veh.") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Control VC, YES, Control NVC, NO, Estimation, OLS) 



///////////////////////////////////////////////////////////////////////////////////////////
///Table A.31//////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////
use "finaldata\final_cc_data.dta", clear
drop if fipsplace_00==.
xtset fipsplace_00 year

drop if year>1991

*Create instrument and outcome
g good_soil = (ph1_plc_wtm_wtm_0_r>=6.8 & ph1_plc_wtm_wtm_0_r<=7.7)
gen inter_goodtetra=good_soil*tetra_corr
gen inter_badtetra=(1-good_soil)*tetra_corr
g perc_cc =  tot_cc_oripop_corr / (tot_ncc_oripop_corr + tot_cc_oripop_corr)
g vc_pc = totnpcc_cc_offenses_vc * 1000
gen nvc_pc = (totnpcc_cc_offenses_17c + totnpcc_cc_offenses_21c)*1000
g vc_pc_1c = totnpcc_cc_offenses_1c
g vc_pc_3c = totnpcc_cc_offenses_3c
g vc_pc_6c = totnpcc_cc_offenses_6c
g vc_pc_11c = totnpcc_cc_offenses_11c
g vc_pc_17c = totnpcc_cc_offenses_17c
g vc_pc_21c = totnpcc_cc_offenses_21c
g vc_pc_22c = totnpcc_cc_offenses_22c

label var inter_badtetra "High LB x Lead"

local effect_vc = 0

reghdfe vc_pc_1c inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
local effect_vc = `effect_vc' + _b[inter_badtetra] * 8982907
local ccost_vc = _b[inter_badtetra] * 8982907
local ccost= string(round(`ccost_vc',.01) , "%9.2f")
outreg2 inter_badtetra using table_vcnvc_cost, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("Murder") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS, Cost, `ccost') replace
reghdfe vc_pc_3c inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
local effect_vc = `effect_vc' + _b[inter_badtetra] * 240776
local ccost_vc = _b[inter_badtetra] * 240776
local ccost= string(round(`ccost_vc',.01) , "%9.2f")
outreg2 inter_badtetra using table_vcnvc_cost, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("Rape") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS, Cost, `ccost') 
reghdfe vc_pc_6c inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
local effect_vc = `effect_vc' + _b[inter_badtetra] * 42310
local ccost_vc = _b[inter_badtetra] * 42310
local ccost= string(round(`ccost_vc',.01) , "%9.2f")
outreg2 inter_badtetra using table_vcnvc_cost, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("Robbery") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS, Cost, `ccost') 
reghdfe vc_pc_11c inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
local effect_vc = `effect_vc' + _b[inter_badtetra] * 107020 
local ccost_vc = _b[inter_badtetra] * 107020
local ccost= string(round(`ccost_vc',.01) , "%9.2f")
outreg2 inter_badtetra using table_vcnvc_cost, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("Assault") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS, Cost, `ccost') 

local effect_nvc = 0

reghdfe vc_pc_17 inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
local effect_nvc = `effect_nvc' + _b[inter_badtetra] * 6462
local ccost_nvc = _b[inter_badtetra] * 6462
local ccost= string(round(`ccost_nvc',.01) , "%9.2f")
outreg2 inter_badtetra using table_vcnvc_cost, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("Burglary") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS, Cost, `ccost') 
reghdfe vc_pc_21 inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
local effect_nvc = `effect_nvc' + _b[inter_badtetra] * 3532
local ccost_nvc = _b[inter_badtetra] * 3532
local ccost= string(round(`ccost_nvc',.01) , "%9.2f")
outreg2 inter_badtetra using table_vcnvc_cost, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("Larceny") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS, Cost, `ccost') 
reghdfe vc_pc_22 inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
local effect_nvc = `effect_nvc' + _b[inter_badtetra] * 10772
local ccost_nvc = _b[inter_badtetra] * 10772
local ccost= string(round(`ccost_nvc',.01) , "%9.2f")
outreg2 inter_badtetra using table_vcnvc_cost, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("Motor Veh.") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS, Cost, `ccost') 

qui: {
noisily: di "EFFECT VIOLENT CRIME: "  `effect_vc'
noisily: di "EFFECT PROPERTY CRIME: "  `effect_nvc'
}



///////////////////////////////////////////////////////////////////////////////////////////
///Table A.33 - A.34 - A.35
///////////////////////////////////////////////////////////////////////////////////////////

use "finaldata\final_cc_data.dta", clear
drop if fipsplace_00==.
xtset fipsplace_00 year

drop if year>1991

*Create instrument and outcome
g good_soil = (ph1_plc_wtm_wtm_0_r>=6.8 & ph1_plc_wtm_wtm_0_r<=7.7)
gen inter_goodtetra=good_soil*tetra_corr
gen inter_badtetra=(1-good_soil)*tetra_corr
g perc_cc =  tot_cc_oripop_corr / (tot_ncc_oripop_corr + tot_cc_oripop_corr)
g vc_pc = totnpcc_cc_offenses_vc * 1000
gen nvc_pc = (totnpcc_cc_offenses_17c + totnpcc_cc_offenses_21c + totnpcc_cc_offenses_22c)*1000
g vc_pc_1c = totnpcc_cc_offenses_1c*1000
g vc_pc_3c = totnpcc_cc_offenses_3c*1000
g vc_pc_6c = totnpcc_cc_offenses_6c*1000
g vc_pc_11c = totnpcc_cc_offenses_11c*1000
g vc_pc_17c = totnpcc_cc_offenses_17c*1000
g vc_pc_21c = totnpcc_cc_offenses_21c*1000
g vc_pc_22c = totnpcc_cc_offenses_22c*1000

g vc_pc_ncc = totnpcc_ncc_offenses_vc * 1000
gen nvc_pc_ncc = (totnpcc_ncc_offenses_17c + totnpcc_ncc_offenses_21c + totnpcc_ncc_offenses_22c)*1000

*Cluster variables
egen cdivcodeyear=group(cdivcode year)

*Label variables
label var totnpcc_cc_offenses_1c "Murder"
label var totnpcc_cc_offenses_2c "Manslaughter"
label var totnpcc_cc_offenses_3c "Rape"
label var totnpcc_cc_offenses_4c "(Forcible)"
label var totnpcc_cc_offenses_5c "(Attempted)"
label var totnpcc_cc_offenses_6c "Robbery"
label var totnpcc_cc_offenses_7c "(Gun)"
label var totnpcc_cc_offenses_8c "(Knife)"
label var totnpcc_cc_offenses_9c "(Other weapon)"
label var totnpcc_cc_offenses_10c "(Strong arm)"
label var totnpcc_cc_offenses_11c "Assaults"
label var totnpcc_cc_offenses_12c "(Gun)"
label var totnpcc_cc_offenses_13c "(Knife)"
label var totnpcc_cc_offenses_14c "(Other weapon)"
label var totnpcc_cc_offenses_15c "(Hand-feet)"
label var totnpcc_cc_offenses_16c "(Simple)"
label var totnpcc_cc_offenses_17c "Burglary"
label var totnpcc_cc_offenses_18c "(Force entry)"
label var totnpcc_cc_offenses_19c "(Entry no force)"
label var totnpcc_cc_offenses_20c "(Attempted)"
label var totnpcc_cc_offenses_21c "Larceny"
label var totnpcc_cc_offenses_22c "(Vehicle theft)"
label var totnpcc_cc_offenses_23c "(Auto theft)"
label var totnpcc_cc_offenses_24c "(Truck-bus theft)"
label var totnpcc_cc_offenses_25c "(Other vehicles)"
label var totnpcc_cc_offenses_26c "Total"
label var totnpcc_cc_offenses_vc "Violent"
label var totnpcc_cc_offenses_ass "Aggr. assaults"

label var inter_badtetra "High LB x Lead"
label var vc_pc "Violent crime"
label var nvc_pc "Property crime"

***Summary statistics violent crime and property crime CC and NCC
file open myfile using table_sumstat_vcnvc.txt, write replace
file write myfile "\begin{tabular}{l ccc c ccc} \hline\hline \textbf{Type of crime} &  \multicolumn{3}{c}{\textbf{1960}} & & \multicolumn{3}{c}{\textbf{1991}} \\ \cline{2-4} \cline{6-8} \\  & \textbf{CC} & \textbf{NCC} & \textbf{CC/NCC} & & \textbf{CC} & \textbf{NCC} & \textbf{CC/NCC} \\  \hline" _n

su vc_pc if year==1960
local aa = trim("`: display %8.2f r(mean)'")
su vc_pc_ncc if year==1960
local bb = trim("`: display %8.2f r(mean)'")
local cc = trim("`: display %8.2f `aa'/`bb''")
su vc_pc if year==1991
local dd = trim("`: display %8.2f r(mean)'")
su vc_pc_ncc if year==1991
local ee = trim("`: display %8.2f r(mean)'")
local ff = trim("`: display %8.2f `dd'/`ee''")

file write myfile ("Violent") _tab "&" %11.2f (`aa') _tab "&"   _tab %11.2f ("`bb'") _tab "&" _tab %11.2f ("`cc'") _tab "&" _tab _tab "&" _tab %8.2f ("`dd'") _tab "&" _tab %8.2f ("`ee'") _tab "&" _tab %8.2f ("`ff'") _tab "\\" _n 

su nvc_pc if year==1960
local aa = trim("`: display %8.2f r(mean)'")
su nvc_pc_ncc if year==1960
local bb = trim("`: display %8.2f r(mean)'")
local cc = trim("`: display %8.2f `aa'/`bb''")
su nvc_pc if year==1991
local dd = trim("`: display %8.2f r(mean)'")
su nvc_pc_ncc if year==1991
local ee = trim("`: display %8.2f r(mean)'")
local ff = trim("`: display %8.2f `dd'/`ee''")

file write myfile ("Property") _tab "&" %11.2f (`aa') _tab "&"   _tab %11.2f ("`bb'") _tab "&" _tab %11.2f ("`cc'") _tab "&" _tab _tab "&" _tab %8.2f ("`dd'") _tab "&" _tab %8.2f ("`ee'") _tab "&" _tab %8.2f ("`ff'") _tab "\\" _n 

file write myfile "\hline \end{tabular}"
file close myfile

***First stage using property and violent crime in CC and NCC

su vc_pc if year==1960
g NORMvc_pc = vc_pc / r(mean) 
su nvc_pc if year==1960
g NORMnvc_pc = nvc_pc / r(mean) 
su nvc_pc_ncc if year==1960
g NORMnvc_pc_ncc = nvc_pc_ncc / r(mean)
su vc_pc_ncc if year==1960
g NORMvc_pc_ncc = vc_pc_ncc / r(mean)

reghdfe NORMvc_pc inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
outreg2 using table_normeff_ccncc, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("Violent CC") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS)  replace
reghdfe NORMvc_pc_ncc inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
outreg2 using table_normeff_ccncc, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("Violent NCC") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS)
reghdfe NORMnvc_pc inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
outreg2 using table_normeff_ccncc, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("Property CC") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS)
reghdfe NORMnvc_pc_ncc inter_badtetra, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
outreg2 using table_normeff_ccncc, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra) label ctitle("Property NCC") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS)

sureg (NORMnvc_pc inter_badtetra i.fipsplace_00 i.year i.cdivcode#i.year) ///
      (NORMnvc_pc_ncc inter_badtetra i.fipsplace_00 i.year i.cdivcode#i.year)
lincom [NORMnvc_pc]inter_badtetra - [NORMnvc_pc_ncc]inter_badtetra

***Reduced form complementarity between instrument and VC-NVC

gen nvc_pc_1960_2=nvc_pc if year==1960
egen nvc_pc_1960=max(nvc_pc_1960_2), by(fipsplace_00)
gen vc_pc_1960_2=vc_pc if year==1960
egen vc_pc_1960=max(vc_pc_1960_2), by(fipsplace_00)
drop nvc_pc_1960_2 vc_pc_1960_2
gen inter_nvc_pc_1960=inter_badtetra*nvc_pc_1960
gen inter_vc_pc_1960=inter_badtetra*vc_pc_1960
label var inter_nvc_pc_1960 "High LB x Lead X Property crime 1960"
label var inter_vc_pc_1960 "High LB x Lead X Violent crime 1960"

reghdfe perc_cc inter_badtetra inter_vc_pc_1960, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
outreg2 using table_het_vcnvc, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra inter_vc_pc_1960) label ctitle("Share Pop CC") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS) replace
reghdfe perc_cc inter_badtetra inter_nvc_pc_1960, absorb(fipsplace_00 year i.cdivcode#i.year) vce(cluster cdivcode#year)
outreg2 using table_het_vcnvc, tex(frag) nocon noni nor2 nonotes keep(inter_badtetra inter_nvc_pc_1960) label ctitle("Share Pop CC") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS)




///////////////////////////////////////////////////////////////////////////////////////////
///Table A.36//////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////

use "finaldata\final_cc_data.dta", clear
drop if fipsplace_00==.
xtset fipsplace_00 year

drop if year>1991

*Create instrument and outcome
g good_soil = (ph1_plc_wtm_wtm_0_r>=6.8 & ph1_plc_wtm_wtm_0_r<=7.7)
gen inter_goodtetra=good_soil*tetra_corr
gen inter_badtetra=(1-good_soil)*tetra_corr
g perc_cc =  tot_cc_oripop_corr / (tot_ncc_oripop_corr + tot_cc_oripop_corr)
g vc_pc = totnpcc_cc_offenses_vc * 1000
gen nvc_pc = (totnpcc_cc_offenses_17c + totnpcc_cc_offenses_21c + totnpcc_cc_offenses_22c)*1000

egen cdivcodeyear=group(cdivcode year)


g crime_pc = (totnpcc_cc_offenses_vc + totnpcc_cc_offenses_17c + totnpcc_cc_offenses_21c + totnpcc_cc_offenses_22c)*1000
g lnpop_cc = ln(tot_cc_oripop_corr)
g lnpop_ncc = ln(tot_ncc_oripop_corr)
g pop_msa = tot_ncc_oripop_corr + tot_cc_oripop_corr
gen lnpop_msa=ln(pop_msa)


label var crime_pc "Crime"
label var inter_badtetra "High LB x Lead"

***First stage
reghdfe crime_pc inter_badtetra, abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
outreg2 inter_badtetra using table_baselineTC, tex(frag) nocon noni nor2 nonotes label ctitle("Crime") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS, F, .)  replace
sleep 1000
***OLS
reghdfe perc_cc crime_pc, abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
outreg2 vc_pc using table_baselineTC, tex(frag) nocon noni nor2 nonotes label ctitle("Sh. Pop CC") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, OLS, F, .) 
sleep 1000
***IV
ivreghdfe perc_cc (crime_pc = inter_badtetra), abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
local ff= string(round(e(cdf),.01) , "%9.2f")
outreg2 vc_pc using table_baselineTC, tex(frag) nocon noni nor2 nonotes label ctitle("Sh. Pop CC") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Estimation, IV, F, `ff') 
sleep 1000
***IV CC
ivreghdfe lnpop_cc (crime_pc = inter_badtetra), abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
local ff= string(round(e(cdf),.01) , "%9.2f")
outreg2 vc_pc using table_baselineTC, tex(frag) nocon noni nor2 nonotes label ctitle("ln(Pop CC)") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES,  Estimation, IV, F, `ff')  
sleep 1000
***IV NCC
ivreghdfe lnpop_ncc (crime_pc = inter_badtetra), abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
local ff= string(round(e(cdf),.01) , "%9.2f")
outreg2 vc_pc using table_baselineTC, tex(frag) nocon noni nor2 nonotes label ctitle("ln(Pop NCC)") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES,  Estimation, IV, F, `ff') 
sleep 1000
***IV MSA
ivreghdfe lnpop_msa (crime_pc = inter_badtetra), abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
local ff= string(round(e(cdf),.01) , "%9.2f")
outreg2 vc_pc using table_baselineTC, tex(frag) nocon noni nor2 nonotes label ctitle("ln(Pop MSA)") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES,  Estimation, IV, F, `ff') 





///////////////////////////////////////////////////////////////////////////////////////////
///Table A.37////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////
use "finaldata\final_cc_data.dta", clear
drop if fipsplace_00==.
xtset fipsplace_00 year

drop if year>1991

*Create instrument and outcome
g good_soil = (ph1_plc_wtm_wtm_0_r>=6.8 & ph1_plc_wtm_wtm_0_r<=7.7)
gen inter_goodtetra=good_soil*tetra_corr
gen inter_badtetra=(1-good_soil)*tetra_corr
g perc_cc =  tot_cc_oripop_corr / (tot_ncc_oripop_corr + tot_cc_oripop_corr)
gen pop_msa=tot_cc_oripop_corr+tot_ncc_oripop_corr
gen vc_pc=totnpcc_cc_offenses_vc*1000
gen pctover25=over25/pop_msa
gen hreturn=rent/value
egen cregcodeyear=group(cregcode year) if year==1960 | year==1970 | year==1980 | year==1990

*Label variables
label var vc_pc "Violent crime"
label var inter_goodtetra "Low LB x Lead"
label var inter_badtetra "High LB x Lead"
label var pctblk "% Blacks MSA"
label var income "Median family income"
label var pctover25 "% people over 25"
label var pct65up "% people over 65"
label var pcths "% people with h.s."
label var rent "Median gross rent"
label var value "Median house value"
label var hreturn "House rent / value"

***Controls
*None
ivreghdfe perc_cc (vc_pc = inter_badtetra) if year==1960 | year==1970 | year==1980 | year==1990, abs(fipsplace_00 year cregcode#year) cluster(cregcodeyear)
local ff = trim("`: display %10.2f e(cdf)'")
outreg2 vc_pc using table_rob_controls, tex(frag) nocon noni nonotes nor2 label ctitle("Share pop CC") addtext(MSA FE, YES, Year FE, YES, C. reg x Year FE, YES, Year, CY 60-90, Estimation, IV, F, `ff') slow(5000) replace
*% Blacks in MSA
ivreghdfe perc_cc  pctblk (vc_pc = inter_badtetra) if year==1960 | year==1970 | year==1980 | year==1990, abs(fipsplace_00 year cregcode#year) cluster(cregcodeyear)
local ff = trim("`: display %10.2f e(cdf)'")
outreg2 vc_pc pctblk using table_rob_controls, tex(frag) nocon noni nonotes nor2 label ctitle("Share pop CC") addtext(MSA FE, YES, Year FE, YES, C. reg x Year FE, YES, Year, CY 60-90, Estimation, IV, F, `ff')  slow(5000)
*Also income in MSA
ivreghdfe perc_cc pctblk income (vc_pc = inter_badtetra) if year==1960 | year==1970 | year==1980 | year==1990, abs(fipsplace_00 year cregcode#year) cluster(cregcodeyear)
local ff = trim("`: display %10.2f e(cdf)'")
outreg2 vc_pc pctblk income using table_rob_controls, tex(frag) nocon noni nonotes nor2 label ctitle("Share pop CC") addtext(MSA FE, YES, Year FE, YES, C. reg x Year FE, YES, Year, CY 60-90, Estimation, IV, F, `ff')  slow(5000)
*Also rent in MSA 
ivreghdfe perc_cc pctblk income rent (vc_pc = inter_badtetra) if year==1960 | year==1970 | year==1980 | year==1990, abs(fipsplace_00 year cregcode#year) cluster(cregcodeyear)
local ff = trim("`: display %10.2f e(cdf)'")
outreg2 vc_pc pctblk income pctover25 pct65up using table_rob_controls, tex(frag) nocon noni nonotes nor2 label ctitle("Share pop CC") addtext(MSA FE, YES, Year FE, YES, C. reg x Year FE, YES, Year, CY 60-90, Estimation, IV, F, `ff')  slow(5000)
*Also house value in MSA 
ivreghdfe perc_cc pctblk income rent value (vc_pc = inter_badtetra) if year==1960 | year==1970 | year==1980 | year==1990, abs(fipsplace_00 year cregcode#year) cluster(cregcodeyear)
local ff = trim("`: display %10.2f e(cdf)'")
outreg2 vc_pc pctblk income pctover25 pct65up using table_rob_controls, tex(frag) nocon noni nonotes nor2 label ctitle("Share pop CC") addtext(MSA FE, YES, Year FE, YES, C. reg x Year FE, YES, Year, CY 60-90, Estimation, IV, F, `ff')  slow(5000)
*Also % people over 25 in MSA
ivreghdfe perc_cc pctblk income rent value pctover25 (vc_pc = inter_badtetra) if year==1960 | year==1970 | year==1980 | year==1990, abs(fipsplace_00 year cregcode#year) cluster(cregcodeyear)
local ff = trim("`: display %10.2f e(cdf)'")
outreg2 vc_pc pctblk income pctover25 using table_rob_controls, tex(frag) nocon noni nonotes nor2 label ctitle("Share pop CC") addtext(MSA FE, YES, Year FE, YES, C. reg x Year FE, YES, Year, CY 70-90, Estimation, IV, F, `ff')  slow(5000)
*Also % people over 65 in MSA 
ivreghdfe perc_cc pctblk income rent value pctover25 pct65up (vc_pc = inter_badtetra) if year==1960 | year==1970 | year==1980 | year==1990, abs(fipsplace_00 year cregcode#year) cluster(cregcodeyear)
local ff = trim("`: display %10.2f e(cdf)'")
outreg2 vc_pc pctblk income pctover25 pct65up using table_rob_controls, tex(frag) nocon noni nonotes nor2 label ctitle("Share pop CC") addtext(MSA FE, YES, Year FE, YES, C. reg x Year FE, YES, Year, CY 70-90, Estimation, IV, F, `ff')  slow(5000)

